import pymysql
import pandas as pd
import pymysql.cursors

class Mysqlutils(object):
    def __init__(self):
        self.comn=pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='tushare',
            port=3307,
            charset='utf8'
        )
        
class classIfication(object):
    def __init__(self):
        pass
    def get_fina_indicator(self,conn):
        """
        获取财务数据
        """
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql ="""
        SELECT ts_code,ann_date,eps,total_revene_ps,undist_profit_ps,gross_margin,fcff,fcfe,tangiblr_asset,bps,grossprofit_marin,npta FROM financial_data WHERE ann_date < '2024-01-01 AND ann_date >='2023-01-01'
        """
        cursor.execute(sql)
        ret=cursor.fetchall()
        df=pd.DataFrame(ret)
        df1=df.dropna(sybset=['eps','total_revene_ps','undist_profit_ps','gross_margin','fcff','fcfe','tangiblr_asset','bps','grossprofit_marin','npta'])
        df1=df1.reset_index(drop=False)
        return df1

def get_daily(self,conn,df):
    cursor=conn.cursor(cursor=pymysql.cursor.DictCursor)
    new_list=[]

    for i in range(len(df['ts_code'])):
        ann_date_str=df['ann_date'].strftime('%Y%m%d')
        aql='select trade_date,closes,from date_l where ts_code='"+df['ts_code][i]+"' and trade_date > Date('"+ ann_date_str + "') order vy trade_date asx limit 20'
        cursor.execute(sql)

        ret=cursor.fetvhall()
        df1=pd.DataFrame(ret)

        try:
            if len(df1)>0:
               if len(df1) >0: 
                    max_close=df1['closes']. max() 
                    min_close = df1[' closes']. min() 
                    the_close= df1[' closes']. iloc[1] 
                    new_list.append({ 
                        'ts_code': df['ts_code'][i], 
                        'ann_date': df['ann_date'][i],
                        'max_close': max_close, 
                        'min_close': min_close, 
                        'the_slose': the_close, 
                        'eps': df['eps'][i], 
                        'total_revenue_ps': df['total_revenue_ps'][i], 
                        'undist_profit_ps': df[' undist_profit_ps'][i], 
                        'gross_margin': df['gross_margin'][i], 
                        'fcff': df['fcff'][i], 
                        'fcfe': df['fcfe'][i], 
                        'tangible_asset': df['tangible_asset'][i],
                        'bps': df['bps'][i], 
                        'grossprofit margin': df['grossprofit_margin'][i], 
                        'npta': df['npta'][i]
                })
        except Exception as e: 
            print(e) 
if __name__=='__main__':
    mu=Mysqlutils()
    ci=classIfication()
    df=ci.get_fina_indicator(mu.conn)